import pymysql
from flask import jsonify, request
import datetime

from app import app


# 管理员登录
def login_manager(username, password):
    # 创建数据库连接
    conn = pymysql.connect(
        host='localhost',  # 连接主机
        user='root',  # 用户名
        passwd='eq10324',  # 密码
        port=3306,  # 端口，默认为3306
        db='order_flask',  # 数据库名称
        charset='utf8'  # 字符编码
    )
    # 生成游标对象 cursor
    cursor = conn.cursor()
    sql = f"select * from manager where Musername = '{username}' and Mpassword = '{password}'"
    print(sql)
    # sql = "select * from manager where Musername = '%s' and Mpassword = '%s'".format(username, password)
    try:
        cursor.execute(sql)
        user = cursor.fetchone()
        if user[2] is not None and user[3] is not None:
            res = "ok"
        else:
            res = "false"
            # return redirect(url_for("manager_dashboard.html"))
    except Exception as e:
        conn.rollback()
        # flash('登录失败，请检查用户名和密码', 'error')
        print(str(e))
        res = "false"
        # return redirect(url_for("login.html"))
    finally:
        cursor.close()  # 关闭游标
        conn.close()  # 关闭连接
    return res


# 用户登录
def login_user(username, password):
    # 创建数据库连接
    conn = pymysql.connect(
        host='localhost',  # 连接主机
        user='root',  # 用户名
        passwd='eq10324',  # 密码
        port=3306,  # 端口，默认为3306
        db='order_flask',  # 数据库名称
        charset='utf8'  # 字符编码
    )
    # 生成游标对象 cursor
    cursor = conn.cursor()
    sql = f"select * from user where Uusername = '{username}' and Upassword = '{password}'"
    # sql = "select * from user where Uusername = %s and Upassword = %s".format(username, password)
    try:
        cursor.execute(sql)
        user = cursor.fetchone()
        if user[2] is not None and user[3] is not None:
            res = "ok"
        else:
            res = "false"
            # return redirect(url_for("user_dashboard.html"))
    except:
        conn.rollback()
        # flash('登录失败，请检查用户名和密码', 'error')
        res = "false"
        # return redirect(url_for("login.html"))
    finally:
        cursor.close()  # 关闭游标
        conn.close()  # 关闭连接
    return res


# 注册
def register(name, username, password, phone):
    conn = pymysql.connect(
        host='localhost',  # 连接主机
        user='root',  # 用户名
        passwd='eq10324',  # 密码
        port=3306,  # 端口，默认为3306
        db='order_flask',  # 数据库名称
        charset='utf8'  # 字符编码
    )
    # 生成游标对象 cursor
    cursor = conn.cursor()
    sql = f"INSERT INTO user VALUES(null,'{name}','{username}','{password}','{phone}')"
    cheak_sql = f"SELECT * FROM user WHERE Uusername = '{username}'"  # 自增主键Uid和Uusername同时构成主键
    # sql = "INSERT INTO user VALUES(NULL,%s,%s,%s,%s)".format(name, username, password, phone)
    # cheak_sql = "SELECT * FROM user WHERE Uusername = %s".format(username)  # 自增主键Uid和Uusername同时构成主键
    try:
        cursor.execute(cheak_sql)
        existing_user = cursor.fetchone()
        if existing_user:
            res = "用户已经存在"
        else:
            cursor.execute(sql)
            # 提交当前游标的全部操作
            conn.commit()
            res = "注册成功"
    except:
        conn.rollback()
        res = "注册失败"
    finally:
        cursor.close()  # 关闭游标
        conn.close()  # 关闭连接
    return res


# 展示菜单接口
def show_dishes():
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            sql = "SELECT * FROM dish"
            try:
                cursor.execute(sql)
                results = cursor.fetchall()
                data = [dict(zip(['Did', 'Dname', 'Dprice', 'Dsale', 'Dinv'], row)) for row in results]
                return data
            except Exception as e:
                conn.rollback()
                print(f"执行 SQL 查询时出错: {e}")
            finally:
                cursor.close()
                conn.close()

    except Exception as e:
        print(f"连接数据库时出错: {e}")


# 下单之前查询用户余额是否充足
def is_purchasable(check, total_price):
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            sql = f"UPDATE wallet SET Waccount = CASE WHEN Waccount >= {total_price} THEN Waccount - {total_price} ELSE Waccount END WHERE Wusername = '{check}'"
            try:
                cursor.execute(sql)
                conn.commit()
                # 检查被修改的行数
                rows_modified = cursor.rowcount

                if rows_modified > 0:
                    return jsonify({"status": "success"})
                else:
                    return jsonify({"status": "error", "message": "No rows were modified."})

            except Exception as e:
                # 处理异常，返回错误的响应
                print(e)
                # return jsonify({"status": "error", "message": str(e)})
            finally:
                cursor.close()
                conn.close()

    except Exception as e:
        print(f"连接数据库时出错: {e}")

nowtime = datetime.datetime.now()
# 下单后修改数据库
def update_database(dish_id, dish_name, dish_price, username, cart):
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            # total_price = sum(item['price'] for item in cart)
            cursor_insert = conn.cursor()
            insert_order_sql = f"INSERT INTO order_form (Oid,Oowner, Oprice,Odate, Oname) VALUES (null,'{username}', {dish_price}, '{nowtime}','{dish_name}');"
            cursor_update = conn.cursor()
            update_order_sql = f"UPDATE dish SET Dinv = Dinv - 1, Dsale = Dsale + 1 WHERE Did = {dish_id}"
            try:
                cursor_insert.execute(insert_order_sql)
                cursor_update.execute(update_order_sql)
                conn.commit()
                # return jsonify({"status": "success"})

            except Exception as e:
                # 处理异常，返回错误的响应
                print(e)
                # return jsonify({"status": "error", "message": str(e)})
            finally:
                cursor_update.close()
                cursor_insert.close()
                conn.close()

    except Exception as e:
        print(f"连接数据库时出错: {e}")


# 管理员端展示所有用户信息
def show_users():
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            sql = "SELECT * FROM user"
            try:
                cursor.execute(sql)
                results = cursor.fetchall()
                data = [dict(zip(['Uid', 'Uname', 'Uusername', 'Upassword', 'Uphone'], row)) for row in results]
                return data
            except Exception as e:
                conn.rollback()
                print(f"执行 SQL 查询时出错: {e}")
            finally:
                cursor.close()
                conn.close()

    except Exception as e:
        print(f"连接数据库时出错: {e}")


def delete_users(Uid):
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            sql = f"DELETE FROM user WHERE Uid='{Uid}'"
            try:
                cursor.execute(sql)
                conn.commit()
                return jsonify({"status": "success"})

            except Exception as e:
                # 处理异常，返回错误的响应
                print(e)
                return jsonify({"status": "error", "message": str(e)})
            finally:
                cursor.close()
                conn.close()

    except Exception as e:
        print(f"连接数据库时出错: {e}")


def delete_dishes(Did):
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            sql = f"DELETE FROM dish WHERE Did='{Did}'"
            try:
                cursor.execute(sql)
                conn.commit()
                return jsonify({"status": "success"})

            except Exception as e:
                # 处理异常，返回错误的响应
                print(e)
                return jsonify({"status": "error", "message": str(e)})
            finally:
                cursor.close()
                conn.close()

    except Exception as e:
        print(f"连接数据库时出错: {e}")


def delete_orders(Oid):
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            sql = f"DELETE FROM order_form WHERE Oid={Oid}"
            try:
                cursor.execute(sql)
                conn.commit()
                return jsonify({"status": "success"})

            except Exception as e:
                # 处理异常，返回错误的响应
                print(e)
                return jsonify({"status": "error", "message": str(e)})
            finally:
                cursor.close()
                conn.close()

    except Exception as e:
        print(f"连接数据库时出错: {e}")


def show_orders():
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            sql = "SELECT * FROM order_form"
            try:
                cursor.execute(sql)
                results = cursor.fetchall()
                data = [dict(zip(['Oid', 'Oowner', 'Oprice', 'Odate', 'Oname'], row)) for row in results]
                return data
            except Exception as e:
                conn.rollback()
                print(f"执行 SQL 查询时出错: {e}")
            finally:
                cursor.close()
                conn.close()

    except Exception as e:
        print(f"连接数据库时出错: {e}")


def add_user(Uname, Uusername, Upassword, Uphone):
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            sql = f"INSERT INTO user VALUES(null,'{Uname}', '{Uusername}', '{Upassword}', '{Uphone}')"
            try:
                cursor.execute(sql)
                conn.commit()
                return jsonify({"status": "success"})

            except Exception as e:
                # 处理异常，返回错误的响应
                print(e)
                return jsonify({"status": "error", "message": str(e)})
            finally:
                cursor.close()
                conn.close()
    except Exception as e:
        print(f"连接数据库时出错: {e}")


def add_dish(Dname, Dprice, Dsale, Dinv):
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            sql = f"INSERT INTO dish VALUES(null,'{Dname}', '{Dprice}', '{Dsale}', '{Dinv}')"
            try:
                cursor.execute(sql)
                conn.commit()
                return jsonify({"status": "success"})

            except Exception as e:
                # 处理异常，返回错误的响应
                print(e)
                return jsonify({"status": "error", "message": str(e)})
            finally:
                cursor.close()
                conn.close()
    except Exception as e:
        print(f"连接数据库时出错: {e}")


def mod_user(Uname, Uusername, Upassword, Uphone, check):
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            sql = f"UPDATE user SET Uname = '{Uname}', Uusername = '{Uusername}', Upassword = '{Upassword}', Uphone = '{Uphone}' WHERE Uusername = '{check}'"
            try:
                cursor.execute(sql)
                conn.commit()
                return jsonify({"status": "success"})

            except Exception as e:
                # 处理异常，返回错误的响应
                print(e)
                return jsonify({"status": "error", "message": str(e)})
            finally:
                cursor.close()
                conn.close()
    except Exception as e:
        print(f"连接数据库时出错: {e}")


def show_account(Wusername):
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            sql = f"SELECT Waccount FROM wallet WHERE Wusername = '{Wusername}'"
            try:
                cursor.execute(sql)
                conn.commit()
                result = cursor.fetchone()
                account = result[0]
                return jsonify({"status": "success", "account": account})

            except Exception as e:
                # 处理异常，返回错误的响应
                print(e)
                return jsonify({"status": "error!!!!!", "message": str(e)})
            finally:
                cursor.close()
                conn.close()
    except Exception as e:
        print(f"连接数据库时出错: {e}")


# 使用事务操作
def user_wallet(Wusername, Waccount):
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            new_cursor = conn.cursor()
            try:
                with conn.cursor() as cursor:
                    sql = "UPDATE wallet SET Waccount = Waccount + %s WHERE Wusername = %s"
                    new_sql = "SELECT Waccount FROM wallet WHERE Wusername = %s"

                    # 使用事务
                    conn.begin()

                    cursor.execute(sql, (Waccount, Wusername))
                    conn.commit()

                    new_cursor.execute(new_sql, (Wusername,))
                    result = new_cursor.fetchone()
                    newAccount = result[0]

                    return jsonify({"status": "success", "newAccount": newAccount})

            except Exception as e:
                # 处理异常，返回错误的响应
                print(e)
                return jsonify({"status": "error", "message": str(e)})
            finally:
                cursor.close()
                conn.close()
    except Exception as e:
        print(f"连接数据库时出错: {e}")


def show_my_order(username):
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            sql = f"SELECT Oid, Oname, Oprice FROM order_form WHERE Oowner = '{username}'"
            try:
                cursor.execute(sql)
                results = cursor.fetchall()
                data = [dict(zip(['Oid', 'Oname', 'Oprice'], row)) for row in results]
                return data
            except Exception as e:
                conn.rollback()
                print(f"执行 SQL 查询时出错: {e}")
            finally:
                cursor.close()
                conn.close()

    except Exception as e:
        print(f"连接数据库时出错: {e}")


def search_dish(keyword):
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            sql = f"SELECT * FROM dish WHERE Dname LIKE '%{keyword}%'"
            try:
                cursor.execute(sql)
                results = cursor.fetchall()
                data = [dict(zip(['Did', 'Dname', 'Dprice', 'Dsale', 'Dinv'], row)) for row in results]
                return data
            except Exception as e:
                conn.rollback()
                print(f"执行 SQL 查询时出错: {e}")
            finally:
                cursor.close()
                conn.close()

    except Exception as e:
        print(f"连接数据库时出错: {e}")


def account_book():
    try:
        with app.app_context():
            conn = pymysql.connect(
                host='localhost',  # 连接主机
                user='root',  # 用户名
                passwd='eq10324',  # 密码
                port=3306,  # 端口，默认为3306
                db='order_flask',  # 数据库名称
                charset='utf8'  # 字符编码
            )
            cursor = conn.cursor()
            sql = "SELECT * FROM OwnerTotalPriceView;"
            try:
                cursor.execute(sql)
                results = cursor.fetchall()
                data = [dict(zip(['Oowner', 'TotalPrice'], row)) for row in results]
                return data
            except Exception as e:
                conn.rollback()
                print(f"执行 SQL 查询时出错: {e}")
            finally:
                cursor.close()
                conn.close()

    except Exception as e:
        print(f"连接数据库时出错: {e}")